﻿USE master;
GO

/* activation of clr engine in sql server */
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

/* sets the trust level for external access */
ALTER DATABASE master SET TRUSTWORTHY ON;
GO

/* you must remove all function before you can delete assembly */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[CSV]') AND type = N'AF')
	DROP AGGREGATE DbExt.CSV
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileWriteAllLines]') AND type = N'AF')
	DROP AGGREGATE DbExt.FileWriteAllLines
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Skewness]') AND type = N'AF')
	DROP AGGREGATE DbExt.Skewness
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Kurtosis]') AND type = N'AF')
	DROP AGGREGATE DbExt.Kurtosis
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Median]') AND type = N'AF')
	DROP AGGREGATE DbExt.Median
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Dir]'))
	DROP FUNCTION DbExt.Dir 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileCopy]'))
	DROP FUNCTION DbExt.FileCopy 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileDelete]'))
	DROP FUNCTION DbExt.FileDelete
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileExists]'))
	DROP FUNCTION DbExt.FileExists 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileMove]'))
	DROP FUNCTION DbExt.FileMove 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileRename]'))
	DROP FUNCTION DbExt.FileRename 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileReadAllLines]'))
	DROP FUNCTION DbExt.FileReadAllLines 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileReadAllText]')) 
	DROP FUNCTION DbExt.FileReadAllText 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FileWriteAllText]'))
	DROP FUNCTION DbExt.FileWriteAllText 
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[ShareConnect]')) 
	DROP FUNCTION DbExt.ShareConnect 
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[ShareDisconnect]'))
	DROP FUNCTION DbExt.ShareDisconnect 
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[DirDelete]'))
	DROP FUNCTION DbExt.DirDelete
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[DirCreate]'))
	DROP FUNCTION DbExt.DirCreate
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[DirExists]'))
	DROP FUNCTION DbExt.DirExists
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[DirMove]'))
	DROP FUNCTION DbExt.DirMove
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[DirRename]'))
	DROP FUNCTION DbExt.DirRename
GO
------------------------------------------------------------------------------------------------
/*
	This section is needed due to compatibility issues
	The following functions were renamed in a early stage
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[StringSplit]'))
	DROP FUNCTION DbExt.StringSplit
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[StringLPad]')) 
	DROP FUNCTION DbExt.StringLPad
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[StringRPad]'))
	DROP FUNCTION DbExt.StringRPad
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[StringSubStr]'))
	DROP FUNCTION DbExt.StringSubStr
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Split]'))
	DROP FUNCTION DbExt.Split
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[LPad]'))
	DROP FUNCTION DbExt.LPad
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[RPad]')) 
	DROP FUNCTION DbExt.RPad
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[SubStr]'))
	DROP FUNCTION DbExt.SubStr
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Remove]'))
	DROP FUNCTION DbExt.Remove
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[TrimStart]'))
	DROP FUNCTION DbExt.TrimStart
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[TrimEnd]'))
	DROP FUNCTION DbExt.TrimEnd
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[StartsWith]'))
	DROP FUNCTION DbExt.StartsWith
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[EndsWith]'))
	DROP FUNCTION DbExt.EndsWith
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Contains]'))
	DROP FUNCTION DbExt.[Contains]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Translate]'))
	DROP FUNCTION DbExt.[Translate]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[StringFormat]'))
	DROP FUNCTION DbExt.[StringFormat]
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FtpUpload]'))
	DROP FUNCTION DbExt.FtpUpload
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[RailDigitSum]'))
	DROP FUNCTION DbExt.RailDigitSum
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[RailCheckDigit]'))
	DROP FUNCTION DbExt.RailCheckDigit
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[ExportToCSV]'))
	DROP FUNCTION DbExt.ExportToCSV
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Zeitraeume]'))
	DROP FUNCTION DbExt.Zeitraeume
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[QueryToCsv]'))
	DROP PROCEDURE DbExt.QueryToCsv;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[QueryToHtml]'))
	DROP PROCEDURE DbExt.QueryToHtml;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[QueryToExcelXml]'))
	DROP PROCEDURE DbExt.QueryToExcelXml;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[QueryToXml]'))
	DROP PROCEDURE DbExt.QueryToXml;
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[Version]'))
	DROP FUNCTION DbExt.[Version]
GO


/* deletes the old assembly */
IF  EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'sqlserverextensions' and is_user_defined = 1)
DROP ASSEMBLY [sqlserverextensions]
GO


/* Loads assembly */
CREATE ASSEMBLY sqlserverextensions
FROM 'D:\INSTALL\sqlserverextensions.dll'
WITH PERMISSION_SET = UNSAFE
GO

/* creates the schema if not exists */
IF SCHEMA_ID('DbExt') IS NULL
	EXECUTE('CREATE SCHEMA DbExt AUTHORIZATION db_owner;')
GO

/* make functions/procedures/types available in schema DbExt */
CREATE AGGREGATE DbExt.CSV(@Value NVARCHAR(255), @Seperator NVARCHAR(255))
RETURNS NVARCHAR(MAX)
EXTERNAL NAME sqlserverextensions.CSV
GO

CREATE AGGREGATE DbExt.FileWriteAllLines(@Value NVARCHAR(255), @Path NVARCHAR(255))
RETURNS BIT
EXTERNAL NAME sqlserverextensions.FileWriteAllLines
GO

CREATE AGGREGATE DbExt.Skewness(@Value FLOAT)
RETURNS float
EXTERNAL NAME sqlserverextensions.Skewness;
GO

CREATE AGGREGATE DbExt.Kurtosis(@Value FLOAT)
RETURNS float
EXTERNAL NAME sqlserverextensions.Kurtosis;
GO

CREATE AGGREGATE DbExt.Median(@Value FLOAT)
RETURNS FLOAT
EXTERNAL NAME sqlserverextensions.Median
GO



CREATE FUNCTION DbExt.Dir(@Path nvarchar(255), @Filter nvarchar(255))
RETURNS  TABLE (
	Name NVARCHAR(MAX) NULL,
	IsDirectory BIT NULL,
	Size BIGINT NULL,
	DateCreated DATETIME NULL,
	DateModified DATETIME NULL,
	FileExtension NVARCHAR(MAX) NULL,
	IsReadOnly BIT NULL,
	DateLastAccess DATETIME NULL,
	DateLastWrite DATETIME NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.Dir
GO

CREATE FUNCTION DbExt.FileCopy(@SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255), @Overwrite BIT)
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileCopy
GO

CREATE FUNCTION DbExt.FileDelete(@Path NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileDelete
GO

CREATE FUNCTION DbExt.FileExists(@Path NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileExists
GO

CREATE FUNCTION DbExt.FileMove(@SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileMove
GO

CREATE FUNCTION DbExt.FileRename(@SourceFileName NVARCHAR(255), @DestFileName NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileRename
GO

CREATE FUNCTION DbExt.FileReadAllLines(@Path nvarchar(255))
RETURNS  TABLE (
	LineNumber BIGINT NULL,
	Line NVARCHAR(MAX) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileReadAllLines
GO

CREATE FUNCTION DbExt.FileReadAllText(@Path NVARCHAR(255))
RETURNS NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileReadAllText
GO

CREATE FUNCTION DbExt.FileWriteAllText(@Path NVARCHAR(255), @Contents NVARCHAR(MAX))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.FileWriteAllText
GO

CREATE FUNCTION DbExt.DirDelete(@Path NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.DirDelete
GO
CREATE FUNCTION DbExt.DirCreate(@Path NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.DirCreate
GO

CREATE FUNCTION DbExt.DirExists(@Path NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.DirExists
GO
CREATE FUNCTION DbExt.DirMove(@SourceDir NVARCHAR(255), @DestDir NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.DirMove
GO
CREATE FUNCTION DbExt.DirRename(@SourceDir NVARCHAR(255), @DestDir NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.DirRename
GO
-------------------------------------------------------------------------------------

CREATE FUNCTION DbExt.ShareConnect(@DriveLetter NVARCHAR(1), @Path NVARCHAR(255), @Username NVARCHAR(255), @Password NVARCHAR(255))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.ShareConnect
GO

CREATE FUNCTION DbExt.ShareDisconnect(@DriveLetter NVARCHAR(1))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.ShareDisconnect
GO

CREATE FUNCTION DbExt.Split(@Values NVARCHAR(MAX), @Seperator NVARCHAR(255))
RETURNS  TABLE (
	Value NVARCHAR(MAX) NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.Split
GO

CREATE FUNCTION DbExt.LPad(@Value NVARCHAR(MAX), @Length SMALLINT, @PaddingChar NCHAR(1))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.LPad
GO

CREATE FUNCTION DbExt.RPad(@Value NVARCHAR(MAX), @Length SMALLINT, @PaddingChar NCHAR(1))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.RPad
GO

CREATE FUNCTION DbExt.SubStr(@Value NVARCHAR(MAX), @StartIndex SMALLINT)
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.SubStr
GO

CREATE FUNCTION DbExt.Remove(@Value NVARCHAR(MAX), @StartIndex SMALLINT, @Count SMALLINT)
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.Remove
GO

CREATE FUNCTION DbExt.TrimStart(@Value NVARCHAR(MAX), @TrimChars NVARCHAR(10))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.TrimStart
GO

CREATE FUNCTION DbExt.TrimEnd(@Value NVARCHAR(MAX), @TrimChars NVARCHAR(10))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.TrimEnd
GO

CREATE FUNCTION DbExt.StartsWith(@Value NVARCHAR(MAX), @StartsWithString NVARCHAR(MAX))
RETURNS  BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.StartsWith
GO

CREATE FUNCTION DbExt.EndsWith(@Value NVARCHAR(MAX), @EndsWithString NVARCHAR(MAX))
RETURNS  BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.EndsWith
GO

CREATE FUNCTION DbExt.[Contains](@Value NVARCHAR(MAX), @Filter NVARCHAR(MAX))
RETURNS  BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[Contains]
GO

CREATE FUNCTION DbExt.[Translate](@Value NVARCHAR(MAX), @AvailableChars NVARCHAR(MAX), @AllowedChars NVARCHAR(MAX))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[Translate]
GO

CREATE FUNCTION DbExt.[StringFormat](@Value NVARCHAR(MAX), @Format NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[StringFormat]
GO
------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DbExt].[FtpUpload]'))
	DROP FUNCTION DbExt.FtpUpload
GO
CREATE FUNCTION DbExt.[FtpUpload](@ftpServer NVARCHAR(128), @ftpUser NVARCHAR(128), @ftpPassword NVARCHAR(128), @ftpPath NVARCHAR(4000), @ftpFilename NVARCHAR(4000), @localPath NVARCHAR(4000), @localFile NVARCHAR(4000))
RETURNS BIT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[FtpUpload]
GO
------------------------------------------------------------------------------------------------
CREATE FUNCTION DbExt.[RailDigitSum](@Value BIGINT)
RETURNS  BIGINT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[RailDigitSum]
GO

CREATE FUNCTION DbExt.[RailCheckDigit](@ValueWithoutCheckDigit NVARCHAR(MAX))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[RailCheckDigit]
GO

CREATE FUNCTION DbExt.[Zeitraeume](@dateTime DATETIME, @statDateTime DATETIME, @zeitraumTyp NVARCHAR(MAX), @istKumuliert BIT)
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[Zeitraeume]
GO

CREATE FUNCTION DbExt.[ExportToCSV](@query NVARCHAR(MAX), @fileName NVARCHAR(MAX))
RETURNS  BIGINT
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[ExportToCSV]
GO
-----------------------------------------------------------------------
CREATE PROCEDURE DbExt.QueryToCsv 
	 @fileName nvarchar(255)
	,@sqlStatement nvarchar(max)
	,@multiQuery bit
	,@columnHeader bit 
	,@fieldDelimiter nvarchar(1)
	,@rowDelimiter nvarchar(2)
AS EXTERNAL NAME sqlserverextensions.StoredProcedures.QueryToCsv;
GO

CREATE PROCEDURE DbExt.QueryToHtml 
	 @fileName nvarchar(255)
	,@sqlStatement nvarchar(max)
	,@multiQuery bit
	,@columnHeader bit
	,@title nvarchar(255)
AS EXTERNAL NAME sqlserverextensions.StoredProcedures.QueryToHtml;
GO

CREATE PROCEDURE DbExt.QueryToExcelXml 
	 @fileName nvarchar(255)
	,@sqlStatement nvarchar(max)
	,@multiQuery bit
	,@columnHeader bit
AS EXTERNAL NAME sqlserverextensions.StoredProcedures.QueryToExcelXml;
GO

CREATE PROCEDURE DbExt.QueryToXml 
	 @fileName nvarchar(255)
	,@sqlStatement nvarchar(max)
	,@multiQuery bit
	,@columnHeader bit
	,@rootNodeName nvarchar(100)
AS EXTERNAL NAME sqlserverextensions.StoredProcedures.QueryToXml;
GO
-----------------------------------------------------------------------
CREATE FUNCTION DbExt.[Version](@VersionPart NVARCHAR(MAX))
RETURNS  NVARCHAR(MAX)
AS
EXTERNAL NAME sqlserverextensions.UserDefinedFunctions.[Version]
GO
